/**
 * 描述: 连接mysql模块
 * 作者: Jack Chen
 * 日期: 2020-06-20
*/


const mysql = require('mysql2');
const config = require('../db/dbConfig');

// 连接mysql
function connect() {
  const { host, user, password, database } = config;
  return mysql.createConnection({
    host,
    user,
    password,
    database
  })
}

// 新建查询连接
function querySql(sql, values) { 
  const conn = connect();
  return new Promise((resolve, reject) => {
    try {
      conn.query(sql, values, (err, res) => {
        if (err) {
          reject(err);
        } else {
          resolve(res);
        }
      })
    } catch (e) {
      reject(e);
    } finally {
      // 释放连接
      conn.end();
    }
  })
}

// 查询一条语句
function queryOne(sql) {
  return new Promise((resolve, reject) => {
    querySql(sql).then(res => {
      console.log('res===',res)
      if (res && res.length > 0) {
        resolve(res[0]);
      } else {
        resolve(null);
      }
    }).catch(err => {
      reject(err);
    })
  })
}

function buildAdvancedQuery(sql, filters) {
  let query = sql;
  const values = [];
  const conditions = [];
  
  for (const [key, value] of Object.entries(filters)) {
    if (value === undefined || value === null) continue;
    
    if (typeof value === 'object' && value.operator) {
      // 处理 { operator: '>', value: 100 } 这样的条件
      conditions.push(`${key} ${value.operator} ?`);
      values.push(value.value);
    } else if (Array.isArray(value)) {
      conditions.push(`${key} IN (${value.map(() => '?').join(',')})`);
      values.push(...value);
    } else if (typeof value === 'string' && value.includes('%')) {
      conditions.push(`${key} LIKE ?`);
      values.push(value);
    } else {
      conditions.push(`${key} = ?`);
      values.push(value);
    }
  }
  
  if (conditions.length > 0) {
    query += ' WHERE ' + conditions.join(' AND ');
  }
  
  return { query, values };
}


module.exports = {
  querySql,
  queryOne,
  buildAdvancedQuery
}